﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SeDuce.IDAL;
using System.Data.SqlClient;

namespace SeDuce.DAL
{
    public class GalleryDAL : IGalleryDAL
    {
        #region SQL
        public const string sqlPics = "SELECT * FROM TbGallery";
        const string sqlUpdatePic = "UPDATE TbGallery SET PicName = {0}, Url = {1} WHERE (ID = {2})";
        const string sqlInsertPic = "INSERT INTO TbGallery (PicName, Url) VALUES ({0},{1})";
        const string sqlDeletePic = "Delete from TbGallery where ID={0}";
        #endregion

        #region Variables
        private static DataAccessObjectBase _dao = null;
        public static DataAccessObjectBase DAO
        {
            get
            {
                if (_dao == null)
                    _dao = new DataAccessObjectBase();
                return _dao;
            }
            set
            {
                _dao = value;
            }
        }
        #endregion

        public System.Data.DataTable GetPics()
        {
            return DAO.Select(sqlPics);
        }

        public bool InsertPics(List<Model.GalleryPic> pics)
        {
            Dictionary<SqlParameter[], string> insertSqls = new Dictionary<SqlParameter[], string>();
            for (int i = 0; i < pics.Count; i++)
            {
                SqlParameter[] parms = new SqlParameter[] { 
                    new SqlParameter ("@PicName",pics[i].PicName),
                    new SqlParameter ("@Url",pics[i].Url)
                  };
                string sql = string.Format(sqlInsertPic, "@PicName", "@Url");
                insertSqls.Add(parms, sql);
            }
            return DAO.ExecuteSqlTran(insertSqls);
        }

        public bool DeletePics(List<int> picIDs)
        {
            Dictionary<SqlParameter[], string> deleteSqls = new Dictionary<SqlParameter[], string>();
            for (int i = 0; i < picIDs.Count; i++)
            {
                SqlParameter[] parms = new SqlParameter[] { 
                    new SqlParameter ("@ID",picIDs[i])
                  };
                string sql = string.Format(sqlDeletePic, "@ID");
                deleteSqls.Add(parms, sql);
            }
            return DAO.ExecuteSqlTran(deleteSqls);
        }


        public bool UpdatePics(List<Model.GalleryPic> pics)
        {
            Dictionary<SqlParameter[], string> updateSqls = new Dictionary<SqlParameter[], string>();
            for (int i = 0; i < pics.Count; i++)
            {
                SqlParameter[] parms = new SqlParameter[] { 
                    new SqlParameter ("@PicName",pics[i].PicName),
                    new SqlParameter ("@Url",pics[i].Url),
                    new SqlParameter ("@ID",pics[i].ID)
                  };
                string sql = string.Format(sqlUpdatePic, "@PicName", "@Url", "@ID");
                updateSqls.Add(parms, sql);
            }
            return DAO.ExecuteSqlTran(updateSqls);
        }
    }
}
